package c.x.jy.common.utils;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.IndexedColors;
 
@SuppressWarnings("all")
public class ExcelFileGenerator {

    private final int SPLIT_COUNT = 5000; //Excel每个工作表的行数

    private List fieldName = null; //excel数据的抬头栏，即名称栏

    private List fieldData = null; //excel导出的实际数据

    private HSSFWorkbook workBook = null;//一个excel文件

    //有参构造器，限定了使用此类时，必须首先构建好两个list参数，并将所需数据放入上述两个list。
    //其中fieldName这个list可以使用泛型约束List<String>
    //fieldData这个可以使用泛型约束List<List<Object>>
    public ExcelFileGenerator(List fieldName, List fieldData) {
        this.fieldName = fieldName;
        this.fieldData = fieldData;
    }

    /**
     * @return HSSFWorkbook
     */
    @SuppressWarnings("unchecked")
	public HSSFWorkbook createWorkbook() {
        workBook = new HSSFWorkbook();//创建一个工作簿
        int rows = fieldData.size();//清点出输入数据的行数
        int sheetNum = 0;//将工作表个数清零
        //根据数据的行数与每个工作表所能容纳的行数，求出需要创建工作表的个数
        if (rows % SPLIT_COUNT == 0) {
            sheetNum = rows / SPLIT_COUNT;
        } else {
            sheetNum = rows / SPLIT_COUNT + 1;
        }

        for (int i = 1; i <= sheetNum; i++) {
            HSSFSheet sheet = workBook.createSheet("Page " + i);//创建工作表
            HSSFRow headRow = sheet.createRow(0); //创建第一栏，抬头栏
            headRow.setHeight((short)(200*3));
            for (int j = 0; j < fieldName.size(); j++) {
                HSSFCell cell = headRow.createCell(j);//创建抬头栏单元格
                //设置单元格格式
                cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                sheet.setColumnWidth(j, 6000);
                HSSFCellStyle style = workBook.createCellStyle();
                HSSFFont font = workBook.createFont();
                font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
                short color = HSSFColor.BLACK.index;
                font.setColor(color);
                style.setFont(font);
                style.setBorderBottom((short) 1);
                style.setBorderLeft((short) 1);
                style.setBorderRight((short) 1);
                style.setBorderTop((short) 1);
                style.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex());
                style.setFillPattern(CellStyle.SOLID_FOREGROUND);
                style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
                //将数据填入单元格
                if (fieldName.get(j) != null) {
                    cell.setCellStyle(style);
                    cell.setCellValue((String) fieldName.get(j));
                } else {
                    cell.setCellStyle(style);
                    cell.setCellValue("-");
                }
            }
            //创建数据栏单元格并填入数据
            for (int k = 0; k < (rows < SPLIT_COUNT ? rows : SPLIT_COUNT); k++) {
                if (((i - 1) * SPLIT_COUNT + k) >= rows)
                    break;
                HSSFRow row = sheet.createRow(k + 1);
                row.setHeight((short)(200*2));
                 List <Object>rowList = (ArrayList<Object>) fieldData.get((i - 1)
                        * SPLIT_COUNT + k);
                HSSFCellStyle style = workBook.createCellStyle();
                style.setBorderBottom((short)1);
                style.setBorderLeft((short)1);
                style.setBorderRight((short)1);
                style.setBorderTop((short)1);
                style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
                for (int n = 0; n < rowList.size(); n++) {
                    HSSFCell cell = row.createCell(n);
                    if (rowList.get(n) != null) {
                        cell.setCellStyle(style);
                        cell.setCellValue((String) rowList.get(n).toString());
                    } else {
                        cell.setCellStyle(style);
                        cell.setCellValue("");
                    }
                }
            }
        }
        return workBook;
    }

    //将信息写入输出流的方法。
    public void exportExcel(OutputStream os) throws Exception {
        workBook = createWorkbook();
        workBook.write(os);
        os.close();
    }
    /**
     * 下载Excel文件
     * @param list   对象数组
     * @param TitleMap  导出值 KEY ，标题
     * @param response  
     * @param fileName 文件名
     * @return
     */
    @SuppressWarnings("rawtypes")
	public static boolean wrteExcelFile(List  list,Map<String,String> TitleMap,HttpServletResponse response,String fileName){

		 List<String> titleList = new ArrayList<String> ();
		 List<String> titleKeyList = new ArrayList<String> ();
		 List<List<Object>> dataList = new ArrayList<List<Object>>();
	     Set<String> keySet = TitleMap.keySet();                  //map.keySet()：获取所有的键的集合，键是String类型
	     Iterator<String> it = keySet.iterator();
          while(it.hasNext()){
			    String key = it.next();
			    titleKeyList.add(key);
			    titleList.add(TitleMap.get(key));  
		  }
		for (Object order : list) { 
		     List<Object>  objlist=new ArrayList<Object>();
		     for (String key : titleKeyList) {
		    		 objlist.add(GetClassinfo.getFieldValueByName(key, order));
			}                           
		        dataList.add(objlist);
		}
		return ExcelFileGenerator.writeExcelFile(titleList, dataList, response, fileName);
    }
    /**
     * 
     * @param titleList 标提
     * @param dataList  数据
     * @param fileName  导入文件名
     * @return
     */
    public static boolean writeExcelFile( List<String> titleList, List<List<Object>> dataList,HttpServletResponse response,String fileName){
    	    OutputStream outputStream=null;
	    	try {
	    		setResponseHeader(response, fileName);
	    		ExcelFileGenerator excelFileGenerator = new ExcelFileGenerator(titleList,dataList);
	    		
	    		outputStream =response.getOutputStream();;
	            excelFileGenerator.exportExcel(outputStream);
	            outputStream.flush();
	             return true;
			} catch (Exception e) {
				e.printStackTrace();
				return false;
			}finally {
				if(outputStream!=null){
					try {
						outputStream.close();
					} catch (IOException e) {
						e.printStackTrace();
					}
				}
			}
    	
    }
    
    //发送响应流方法
    private static void setResponseHeader(HttpServletResponse response, String fileName) throws Exception {
            fileName = new String(fileName.getBytes(),"ISO8859-1");
            response.setContentType("application/octet-stream;charset=ISO8859-1");
            response.setHeader("Content-Disposition", "attachment;filename="+ fileName);
            response.addHeader("Pargam", "no-cache");
            response.addHeader("Cache-Control", "no-cache");
    
     
    }
   

    public static void main(String[] args){
        List<String> list = new ArrayList<>();
        list.add("第一列");
        list.add("第二列");

        List<List<Object>> l = new ArrayList<>();
        l.add(new ArrayList<Object>(){ {add("一个");add("两个");}});
        l.add(new ArrayList<Object>(){ {add("一个1");add("两个2");}});

        ExcelFileGenerator excelFileGenerator = new ExcelFileGenerator(list,l);
        try {
            OutputStream outputStream = new FileOutputStream("E:\\my.xls");
            excelFileGenerator.exportExcel(outputStream);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }

    }
/**
 * 转换对象数组  Excel List格式  list.get(0) 标题 ，list.get(1) 数据
 * @param list
 * @param titleMap
 * @return
 */
	@SuppressWarnings("rawtypes")
	public static List transform(List list, Map<String, String> titleMap) {
		 List<String> titleList = new ArrayList<String> ();
		 List<String> titleKeyList = new ArrayList<String> ();
		 List<List<Object>> dataList = new ArrayList<List<Object>>();
	     Set<String> keySet = titleMap.keySet();                  //map.keySet()：获取所有的键的集合，键是String类型
	     Iterator<String> it = keySet.iterator();
          while(it.hasNext()){
			    String key = it.next();
			    titleKeyList.add(key);
			    titleList.add(titleMap.get(key));  
		  }
		for (Object order : list) { 
		     List<Object>  objlist=new ArrayList<Object>();
		     for (String key : titleKeyList) {
		    	 try {
		    		 objlist.add(getValueByObject(order, key));
					//objlist.add( PropertyUtils.getProperty(order,key));
				} catch ( Exception e) {
					e.printStackTrace();
					objlist.add("");
				}  
			}                           
		        dataList.add(objlist);
		}
		List<List> resList= new ArrayList<List> ();
		resList.add(titleList);
		resList.add(dataList);
		return resList;
	}
	
	public static Object getValueByObject(Object o ,String fieldName){
	   Object val=	GetClassinfo.getFieldValueByName(fieldName, o);
	 
		return val;
	}
}
